﻿using System;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Net.Mail;
using System.Net;
using System.Data.SqlClient;
using System.Data.OleDb;
using WebMsgBoxNamespace;
using System.Text;





namespace VariablesGlobalesNamespace
{
    static public class GlobalClass
    {
        #region VARIABLES LOGIN
        
        static private int m_IdUsuario = 0;

        public static int IdUsuario
        {
            get { return m_IdUsuario; }
            set { m_IdUsuario = value; }
        }

     
        #endregion

        #region MAPA

        static private bool m_CargarMapa = true;

        public static bool CargarMapa
        {
            get { return m_CargarMapa; }
            set { m_CargarMapa = value; }
        }    

        #endregion 

        #region METODOS ACTUALIZAR HEADER
        static public void ActualizarUsuario(Label LabelUsuario)
        {
        }
        static public void ActualizarBienvenidoUsuario(Label LabelBienvenido)
        {
         
        }
        static public void ActualizarFecha(Label LabelFecha)
        {
            DateTime FechaActual;
            FechaActual = DateTime.Now;
            switch (FechaActual.DayOfWeek.ToString())
                {
                    case "Sunday":
                        LabelFecha.Text = "Domingo";
                        break;
                    case "Monday":
                        LabelFecha.Text = "Lunes";
                        break;
                    case "Tuesday":
                        LabelFecha.Text = "Martes";
                        break;
                    case "Wednesday":
                        LabelFecha.Text = "Miércoles";
                        break;
                    case "Thursday":
                        LabelFecha.Text = "Jueves";
                        break;
                    case "Friday":
                        LabelFecha.Text = "Viernes";
                        break;
                    case "Saturday":
                        LabelFecha.Text = "Sábado";
                        break;
                }
            LabelFecha.Text += " " + FechaActual.Day + " de ";
            switch (FechaActual.Month)
            {
                case 1:
                    LabelFecha.Text += "Enero de " + FechaActual.Year.ToString();
                    break;
                case 2:
                    LabelFecha.Text += "Febrero de " + FechaActual.Year.ToString();
                    break;
                case 3:
                    LabelFecha.Text += "Marzo de " + FechaActual.Year.ToString();
                    break;
                case 4:
                    LabelFecha.Text += "Abril de " + FechaActual.Year.ToString();
                    break;
                case 5:
                    LabelFecha.Text += "Mayo de " + FechaActual.Year.ToString();
                    break;
                case 6:
                    LabelFecha.Text += "Junio de " + FechaActual.Year.ToString();
                    break;
                case 7:
                    LabelFecha.Text += "Julio de " + FechaActual.Year.ToString();
                    break;
                case 8:
                    LabelFecha.Text += "Agosto de " + FechaActual.Year.ToString();
                    break;
                case 9:
                    LabelFecha.Text += "Septiembre de " + FechaActual.Year.ToString();
                    break;
                case 10:
                    LabelFecha.Text += "Octubre de " + FechaActual.Year.ToString();
                    break;
                case 11:
                    LabelFecha.Text += "Noviembre de " + FechaActual.Year.ToString();
                    break;
                case 12:
                    LabelFecha.Text += "Diciembre de " + FechaActual.Year.ToString();
                    break;
            }
        }
        #endregion

            }

    public class Clientes_HORSEJACK
    {
       public string Nombre;
       public string Apellido;
       public string Usuario;
       public int id;
       public string Password;
       public string Email;
       public string LatitudCent;
       public string LongitudCent;
    }

    public class Equipos_En_Caballos_HORSEJACK
    {
       public int idCaballo;
       public string Nombre;
       public string SIM_CodArea;
       public string Telefono;
    };

    public class MetodosHorsejack
    {
        public bool ActCoordenadasPorIMEI(string sIMEI, string sLatitud, string sLongitud)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand();

            sCommand.Connection = sConn;
            sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

            sCommand.CommandType = CommandType.Text;
            sCommand.CommandText = "UPDATE Caballos_HORSEJACK       "+
                                   " SET Caballos_HORSEJACK.Latitud=@Latitud,Caballos_HORSEJACK.Longitud=@Longitud,Caballos_HORSEJACK.UltimaActualizacion=GETDATE() "+
                                   " FROM  Caballos_HORSEJACK INNER JOIN Equipos_HORSEJACK ON "+ 
                                   "       Caballos_HORSEJACK.idEquipo = Equipos_HORSEJACK.idEquipo "+
                                   " WHERE Equipos_HORSEJACK.IMEI = @IMEI AND Caballos_HORSEJACK.Habilitado=1";

            sCommand.Parameters.Add(new SqlParameter("@Latitud", SqlDbType.VarChar,15)).Value = sLatitud;
            sCommand.Parameters.Add(new SqlParameter("@Longitud", SqlDbType.VarChar,15)).Value = sLongitud;
            sCommand.Parameters.Add(new SqlParameter("@IMEI", SqlDbType.VarChar, 20)).Value = sIMEI;
            sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            try
            {
                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                while (sReader.Read())
                {
                    //     Read your data 
                }

            }
            catch
            {
                sConn.Close();
                return false;
 
            }
            sConn.Close();

            return true;
        }


        public bool LoginUsuario(TextBox TextBox_Usuario, TextBox TextBox_Password, out int idUsuario,
                                        out string Latitud, out string Longitud)
        {

            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            SqlCommand sCommand = new SqlCommand("SP_Login_Usuario", sConn);


            try
            {

                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";


                sCommand.CommandType = CommandType.StoredProcedure;
                sCommand.Parameters.Add(new SqlParameter("@Usuario", SqlDbType.VarChar)).Value = TextBox_Usuario.Text;
                sCommand.Parameters.Add(new SqlParameter("@Pwd", SqlDbType.VarChar)).Value = TextBox_Password.Text;
                sCommand.Parameters.Add(new SqlParameter("@idUsuario", SqlDbType.Int)).Value = 0;
                sCommand.Parameters.Add(new SqlParameter("@LatC", SqlDbType.VarChar, 20)).Value = "";
                sCommand.Parameters.Add(new SqlParameter("@LongC", SqlDbType.VarChar, 20)).Value = "";
                sCommand.Parameters["@LatC"].Direction = ParameterDirection.InputOutput;
                sCommand.Parameters["@LongC"].Direction = ParameterDirection.InputOutput;
                sCommand.Parameters["@idUsuario"].Direction = ParameterDirection.InputOutput;
                sCommand.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;



                sConn.Open();

                sCommand.Connection = sConn;
                sReader = sCommand.ExecuteReader();


                while (sReader.Read())
                {
                    //     Read your data 
                }


                sConn.Close();
                string sUsuario = sCommand.Parameters["@LatC"].Value.ToString();
                idUsuario = (int)sCommand.Parameters["@idUsuario"].Value;
                Latitud = (string)sCommand.Parameters["@LatC"].Value;
                Longitud = (string)sCommand.Parameters["@LongC"].Value;

                int sValor = sUsuario.CompareTo("");


                if (sValor != 0)
                {
                    return true;
                }
                else
                {
                    WebMsgBox.Show("El usuario o el password es incorrecto. Intente nuevamente por favor");
                    return false;
                }

            }
            catch (Exception Except)
            {
                WebMsgBox.Show("Error en la página. Contáctese con Soporte Técnico. Mensaje:" + Except.Message);
                idUsuario = 0;
                Latitud = "";
                Longitud = "";
                return false;

            }
        }
        #region Mapa
        public bool ActualizarMapa(int idUsuario, string sLatitud, string sLongitud, out string NextUrl)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            string sLatitudCentral = sLatitud;
            string sLongitudCentral = sLongitud;

            //   Response.Write(sLatitudCentral);
            //   Response.Write(sLongitudCentral);

            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_Request_Nodes_Position", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;
                sHorses.Parameters.Add(new SqlParameter("@idCliente", SqlDbType.Int)).Value = idUsuario;
                sHorses.Parameters.Add(new SqlParameter("@Count", SqlDbType.Int)).Value = 0;
                sHorses.Parameters["@Count"].Direction = ParameterDirection.InputOutput;

                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;
                sReader = sHorses.ExecuteReader();

                string sNombreCaballo = "";
                string sLatitudCaballo = "";
                string sLongitudCaballo = "";
                string sUltimaActualizacion = "";
                String sHorsesInfo = "&";
                int sHorsesCount = 0;
                String sNombreCaballoLen = "";
                String sLatitudCaballoLen = "";
                String sLongitudCaballoLen = "";

                while (sReader.Read())
                {

                    sNombreCaballo = sReader.GetValue(0).ToString();
                    sLatitudCaballo = sReader.GetValue(1).ToString();
                    sLongitudCaballo = sReader.GetValue(2).ToString();
                    sNombreCaballoLen = String.Format("{0:000}", sNombreCaballo.Length);
                    sLatitudCaballoLen = String.Format("{0:000}", sLatitudCaballo.Length);
                    sLongitudCaballoLen = String.Format("{0:000}", sLongitudCaballo.Length);


                    sHorsesInfo += "*N=" + sNombreCaballoLen + sNombreCaballo + "?La=" + sLatitudCaballoLen + sLatitudCaballo + "?Lo=" + sLongitudCaballoLen + sLongitudCaballo + "?$";
                    sHorsesCount++;
                }


                sConn.Close();


                sConn.Open();
                SqlCommand sUltimaAct = new SqlCommand("SP_UltimaActualizacion", sConn);
                sUltimaAct.CommandType = CommandType.StoredProcedure;
                sUltimaAct.Parameters.Add(new SqlParameter("@idCliente", SqlDbType.Int)).Value = idUsuario;
                sUltimaAct.Parameters.Add(new SqlParameter("@UltimaActualizacion", SqlDbType.VarChar, 20)).Value = "1/1/10";
                sUltimaAct.Parameters["@UltimaActualizacion"].Direction = ParameterDirection.InputOutput;

                sUltimaAct.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sUltimaAct.Connection = sConn;
                sReader = sUltimaAct.ExecuteReader();

                while (sReader.Read())
                {
                }


                sConn.Close();

                sUltimaActualizacion = sUltimaAct.Parameters["@UltimaActualizacion"].Value.ToString();
                string sAux = "";

                for (int i = 0; i < sUltimaActualizacion.Length; )
                {
                    if (sUltimaActualizacion[i] == ':')
                    {
                        i++;
                    }
                    else
                    {
                        sAux += sUltimaActualizacion[i];
                        i++;
                    }

                }
                sUltimaActualizacion = sAux;


                NextUrl = "~/Tracking.aspx?Lati=" + sLatitudCentral + "?Long=" + sLongitudCentral + "?Cc=" +
                                 sHorsesCount.ToString() + "?Ua=" + sUltimaActualizacion + "?Uid=" + idUsuario.ToString() +
                                 sHorsesInfo;

                return true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Error en método actualizar: Razón:" + ee.Message);
                NextUrl = "~/login.aspx";
                return false;
            }

        }

        public bool ActualizarCoordenadas(int idCaballo, string sLatitud, string sLongitud)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
                      
            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_Modificar_Coordenadas_Caballo", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;
                sHorses.Parameters.Add(new SqlParameter("@Lat", SqlDbType.VarChar,15)).Value = sLatitud;
                sHorses.Parameters.Add(new SqlParameter("@Long", SqlDbType.VarChar,15)).Value = sLongitud;
                sHorses.Parameters.Add(new SqlParameter("@idCliente", SqlDbType.Int)).Value = idCaballo;
             
                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;
                sReader = sHorses.ExecuteReader();             

                while (sReader.Read())
                {

                }


                sConn.Close();               
                return true;
            }
            catch (Exception ee)
            {
                return false;
            }

        }
        #endregion

        #region Informacion
        public bool ObtenerListaClientes(out int[] idClientes,out string[] sUsuario,
                                        out string[] sNombre, out string[] sApellido,out string[] sEmail,
                                        out string[] sLatC, out string[] sLongC,out int iReadings)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet=false;

            int[] lidClientes = new int[100];
            string[] lsUsuario = new string[100];
            string[] lsNombre = new string[100];
            string[] lsApellido = new string[100];
            string[] lsEmail = new string[100];
            string[] lsLatc = new string[100];
            string[] lsLongc = new string[100];
            int iClientesLeidos = 0;

            try
                {
                    sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                    sConn.Open();
                    SqlCommand sHorses = new SqlCommand("SP_Obtener_Lista_Clientes", sConn);
                    sHorses.CommandType = CommandType.StoredProcedure;

                    sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                    sHorses.Connection = sConn;
                    sReader = sHorses.ExecuteReader();

                   



                    while (sReader.Read())
                    {
                        lidClientes[iClientesLeidos] = Convert.ToInt32(sReader.GetValue(0).ToString());
                        lsUsuario[iClientesLeidos] = sReader.GetValue(1).ToString();
                        lsNombre[iClientesLeidos] = sReader.GetValue(2).ToString();
                        lsApellido[iClientesLeidos] = sReader.GetValue(3).ToString();
                        lsEmail[iClientesLeidos] = sReader.GetValue(5).ToString();
                        lsLatc[iClientesLeidos] = sReader.GetValue(6).ToString();
                        lsLongc[iClientesLeidos] = sReader.GetValue(7).ToString();


                        iClientesLeidos++;
                    }


                    sConn.Close();


                    boRet = true;
                }
                catch (Exception ee)
                {
                    boRet = false;
                }

            idClientes = lidClientes;
            sUsuario = lsUsuario;
            sNombre = lsNombre; 
            sApellido = lsApellido;
            sEmail = lsEmail;
            sLatC = lsLatc;
            sLongC = lsLongc;
            iReadings = iClientesLeidos;
            return boRet;

        }

        public bool ObtenerCaballos(ref int[] idCaballos,ref string[] sNombre,ref int []idEquipo,ref bool[] boHabilitado,
                                    ref string[] sLat,ref string[] sLong,ref string [] sUltAct,ref int[]idGenero, ref string[] sSIM_CodArea,
                                    ref string[] sTelefono,ref int iCaballosLeidos,int idCliente)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;

            

            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_Obtener_Caballos", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;


                sHorses.Parameters.Add("@idCliente", SqlDbType.Int).Value = idCliente;
                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;
                sReader = sHorses.ExecuteReader();

                iCaballosLeidos = 0;



                while (sReader.Read())
                {
                    idCaballos[iCaballosLeidos] = Convert.ToInt32(sReader.GetValue(0).ToString());
                    sNombre[iCaballosLeidos]= sReader.GetValue(1).ToString();
                    idEquipo[iCaballosLeidos] = Convert.ToInt32(sReader.GetValue(2).ToString());
                    sUltAct[iCaballosLeidos] = sReader.GetValue(3).ToString();
                    idGenero[iCaballosLeidos] = Convert.ToInt32(sReader.GetValue(4).ToString());
                    boHabilitado[iCaballosLeidos] = Convert.ToBoolean(sReader.GetValue(5));
                    sLat[iCaballosLeidos] = sReader.GetValue(6).ToString();
                    sLong[iCaballosLeidos] = sReader.GetValue(7).ToString();
                    sSIM_CodArea[iCaballosLeidos] = sReader.GetValue(8).ToString();
                    sTelefono[iCaballosLeidos] = sReader.GetValue(9).ToString();
                    
                    
                    iCaballosLeidos++;
                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                boRet = false;
            }

        
            return boRet;

        }
        #endregion

        #region Clientes
        public bool AgregarCliente(string sUsuario,string sNombre,string sApellido,string sPassword,string sEmail,
                                string sLatc,string sLongc)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_AgregarClientes", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;


                sHorses.Parameters.Add("@Usuario", SqlDbType.VarChar,50).Value = sUsuario;
                sHorses.Parameters.Add("@Nombre", SqlDbType.VarChar, 50).Value = sNombre;
                sHorses.Parameters.Add("@Apellido", SqlDbType.VarChar, 50).Value = sApellido;
                sHorses.Parameters.Add("@Password", SqlDbType.VarChar, 10).Value = sPassword;
                sHorses.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = sEmail;
                sHorses.Parameters.Add("@Latc", SqlDbType.VarChar, 20).Value = sLatc;
                sHorses.Parameters.Add("@Lonc", SqlDbType.VarChar, 20).Value = sLongc;

                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();

               


                while (sReader.Read())
                {
               
                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Excepcion:"+ee.Message.ToString());

                boRet = false;
            }

           
            return boRet;

        }

        public bool BorrarCliente(int iIdCliente)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_BorrarCliente", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;


                sHorses.Parameters.Add("@idCliente", SqlDbType.Int).Value = iIdCliente;
                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();




                while (sReader.Read())
                {

                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Excepcion:" + ee.Message.ToString());

                boRet = false;
            }


            return boRet;

        }

        public bool ModificarCliente(int iIdCliente,string sUsuario, string sNombre, string sApellido, string sPassword, string sEmail,
                              string sLatc, string sLongc,bool boIsUser)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";
                SqlCommand sHorses = new SqlCommand();
                sConn.Open();
                if (boIsUser == true)
                {
                    sHorses.CommandText = "SP_Modificar_Cliente";
                }
                else
                {
                    sHorses.CommandText = "SP_Modificar_Cliente_NP";
                    
               
                }
                sHorses.CommandType = CommandType.StoredProcedure;

                sHorses.Parameters.Add("@idCliente", SqlDbType.Int).Value = iIdCliente;
                sHorses.Parameters.Add("@Usuario", SqlDbType.VarChar, 50).Value = sUsuario;
                sHorses.Parameters.Add("@Nombre", SqlDbType.VarChar, 50).Value = sNombre;
                sHorses.Parameters.Add("@Apellido", SqlDbType.VarChar, 50).Value = sApellido;
                if (boIsUser == true)
                {
                    sHorses.Parameters.Add("@Password", SqlDbType.VarChar, 10).Value = sPassword;
                }
                sHorses.Parameters.Add("@Email", SqlDbType.VarChar, 50).Value = sEmail;
                sHorses.Parameters.Add("@Latc", SqlDbType.VarChar, 20).Value = sLatc;
                sHorses.Parameters.Add("@Lonc", SqlDbType.VarChar, 20).Value = sLongc;

                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();




                while (sReader.Read())
                {

                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Excepcion:" + ee.Message.ToString());

                boRet = false;
            }


            return boRet;

        }

        #endregion

        #region objCaballo
        public bool AgregarCaballo(string sNombre, int iIdCliente, int iIdEquipo, bool bHabilitado, string sLatitud,
                             string sLongitud, string sUltimaActualizacion,int iIdGenero)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_AgregarCaballo", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;


                sHorses.Parameters.Add("@Nombre", SqlDbType.VarChar, 50).Value = sNombre;
                sHorses.Parameters.Add("@idCliente", SqlDbType.Int).Value = iIdCliente;
                sHorses.Parameters.Add("@idEquipo", SqlDbType.Int).Value = iIdEquipo;
                ///TODO: 
           //     sHorses.Parameters.Add("@idCategoriaCaballo", SqlDbType.Int).Value = iIdEquipo;
                sHorses.Parameters.Add("@Habilitado", SqlDbType.Bit).Value = bHabilitado;
                sHorses.Parameters.Add("@Latitud", SqlDbType.VarChar, 15).Value = sLatitud;
                sHorses.Parameters.Add("@Longitud", SqlDbType.VarChar, 15).Value = sLongitud;
                sHorses.Parameters.Add("@UltimaActualizacion", SqlDbType.DateTime).Value = sUltimaActualizacion;
                sHorses.Parameters.Add("@idGenero", SqlDbType.Int).Value = iIdGenero;
                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();




                while (sReader.Read())
                {

                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Excepcion:" + ee.Message.ToString());

                boRet = false;
            }


            return boRet;

        }
        
        public bool BorrarCaballo(int iIdCaballo)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_BorrarCaballo", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;


                sHorses.Parameters.Add("@idCaballo", SqlDbType.Int).Value = iIdCaballo;
                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();




                while (sReader.Read())
                {

                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Excepcion:" + ee.Message.ToString());

                boRet = false;
            }


            return boRet;

        }

        public bool ModificarCaballo(int iIdCaballo,string sNombre, int iIdCliente, int iIdEquipo, bool bHabilitado, string sLatitud,
                         string sLongitud, string sUltimaActualizacion, int iIdGenero)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_ModificarCaballo", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;

                sHorses.Parameters.Add("@idCaballo", SqlDbType.Int).Value = iIdCaballo;
                sHorses.Parameters.Add("@Nombre", SqlDbType.VarChar, 50).Value = sNombre;
                sHorses.Parameters.Add("@idCliente", SqlDbType.Int).Value = iIdCliente;
                sHorses.Parameters.Add("@idEquipo", SqlDbType.Int).Value = iIdEquipo;
                sHorses.Parameters.Add("@Habilitado", SqlDbType.Bit).Value = bHabilitado;
                sHorses.Parameters.Add("@Latitud", SqlDbType.VarChar, 15).Value = sLatitud;
                sHorses.Parameters.Add("@Longitud", SqlDbType.VarChar, 15).Value = sLongitud;
                sHorses.Parameters.Add("@UltimaActualizacion", SqlDbType.DateTime).Value = sUltimaActualizacion;
                sHorses.Parameters.Add("@idGenero", SqlDbType.Int).Value = iIdGenero;
                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();




                while (sReader.Read())
                {

                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Excepcion:" + ee.Message.ToString());

                boRet = false;
            }


            return boRet;

        }

        #endregion

        #region Equipos
        public bool AgregarEquipo(string sSIM_CodArea,string sTelefono, string sFechaInstalacion,int iReparaciones,int idOperador)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                string CnnString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConexionDatattec"].ConnectionString;
      
              //  sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";
                sConn.ConnectionString = CnnString;

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_AgregarEquipos", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;


                sHorses.Parameters.Add("@SIM_CodArea", SqlDbType.VarChar,6).Value = sSIM_CodArea;
                sHorses.Parameters.Add("@Telefono", SqlDbType.VarChar, 15).Value = sTelefono;
                sHorses.Parameters.Add("@FechaInstalacion", SqlDbType.DateTime).Value = sFechaInstalacion;
                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();




                while (sReader.Read())
                {

                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Excepcion:" + ee.Message.ToString());

                boRet = false;
            }


            return boRet;

        }

        public bool BorrarEquipo(int iIdEquipo)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_BorrarEquipo", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;


                sHorses.Parameters.Add("@idEquipo", SqlDbType.Int).Value = iIdEquipo;
                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();




                while (sReader.Read())
                {

                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Excepcion:" + ee.Message.ToString());

                boRet = false;
            }


            return boRet;

        }

        public bool ModificarEquipo(int iIdEquipo,string sSIM_CodArea, string sTelefono, string sFechaInstalacion, int iReparaciones, int idOperador)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";

                sConn.Open();
                SqlCommand sHorses = new SqlCommand("SP_ModificarEquipo", sConn);
                sHorses.CommandType = CommandType.StoredProcedure;


                sHorses.Parameters.Add("@iIdEquipo", SqlDbType.Int).Value = iIdEquipo;
                sHorses.Parameters.Add("@SIM_CodArea", SqlDbType.VarChar, 6).Value = sSIM_CodArea;
                sHorses.Parameters.Add("@Telefono", SqlDbType.VarChar, 15).Value = sTelefono;
                sHorses.Parameters.Add("@FechaInstalacion", SqlDbType.DateTime).Value = sFechaInstalacion;
                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();




                while (sReader.Read())
                {

                }


                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                WebMsgBox.Show("Excepcion:" + ee.Message.ToString());

                boRet = false;
            }


            return boRet;

        }


        public bool DDLEquipos(ref int[] idEquipo, ref string[] sTelefono)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";
                SqlCommand sHorses = new SqlCommand();
                sConn.Open();


                sHorses.CommandText = "SELECT idEquipo,Telefono FROM Equipos_HORSEJACK";
                sHorses.CommandType = CommandType.Text;

                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();

                int i = 0;
                while (sReader.Read())
                {
                    idEquipo[i] = Convert.ToInt32(sReader.GetValue(0).ToString());
                    sTelefono[i] = sReader.GetValue(1).ToString();
                }



                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                boRet = false;
            }

            return boRet;
        }


        #endregion

        public string FormateoFechaAct(string sFecha)
        {
            string sAno= sFecha.Substring(0,4);
            string sMes = sFecha.Substring(4, 2);
            string sDia = sFecha.Substring(6, 2);
            string sHora = sFecha.Substring(8, 2);
            string sMinuto = sFecha.Substring(10, 2);
            string sSeg = sFecha.Substring(12, 2);

            switch (sMes)
                {
                case "01":
                        sMes = "Enero";
                        break;
                case "02":
                        sMes = "Febrero";
                        break;
                case "03":
                        sMes = "Marzo";
                        break;
                case "04":
                        sMes = "Abril";
                        break;
                case "05":
                        sMes = "Mayo";
                        break;
                case "06":
                        sMes = "Junio";
                        break;
                case "07":
                        sMes = "Julio";
                        break;
                case "08":
                        sMes = "Agosto";
                        break;
                case "09":
                        sMes = "Septiembre";
                        break;
                case "10":
                        sMes = "Octubre";
                        break;
                case "11":
                        sMes = "Noviembre";
                        break;
                case "12":
                        sMes = "Diciembre";
                        break;

                }
            string sRes;
            sRes = sDia + " de " + sMes + " de " + sAno + " a las " + sHora + ":" + sMinuto + ":" + sSeg;
            return sRes;
        }

        public bool DDLClientes (ref int[] idClientes,ref string[] sValores)
        {
            SqlConnection sConn = new SqlConnection();
            SqlDataReader sReader;
            bool boRet = false;


            try
            {
                sConn.ConnectionString = "data source=.;integrated security=SSPI;initial catalog=sp000049_HorsejackSQL";
                SqlCommand sHorses = new SqlCommand();
                sConn.Open();
              
               
                sHorses.CommandText = "SELECT idCliente,Apellido,Nombre FROM Clientes_HORSEJACK";
                sHorses.CommandType = CommandType.Text;

                sHorses.Parameters.Add("@Return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                sHorses.Connection = sConn;


                sReader = sHorses.ExecuteReader();

                int i = 0;
                while (sReader.Read())
                {
                    idClientes[i] = Convert.ToInt32(sReader.GetValue(0).ToString());
                    sValores[i] = sReader.GetValue(1).ToString() + "," + sReader.GetValue(2).ToString();
                }



                sConn.Close();


                boRet = true;
            }
            catch (Exception ee)
            {
                boRet = false;
            }

            return boRet;
        }

    }
}
